<?php
/*
	Version   :  2.5
	Created   :  20090211
	Modified  :  20091230
	Author    :  Pablo Ramirez
	co-Author :
	Descr     : Controlador "Invoice"
	Changelog : 0.1 - Version en Desarrollo
				1.0 - Version Inicial
				1.1 - Modificacion para agrupar por depot y service_type
				1.2 - Creacion de Reportes Excel
				1.3 - Mejoras en Reportes Excel, cambio a version 1.1 de php-excel.class y mejoras solicitadas
				1.4 - Control de Acceso
				1.5 - Mejoras en reporte, suma de totales, cuenta de registros y separacion por service_type
				1.6 - Se agrega cou_codigo a la busqueda de price_list
				1.7 - Se soluciona filtrado de containers MACH que no tienen BOX en reporte
				1.8 - Se agrega validacion de perfil para mostrar precios
				2.0 - Se realizan las modificaciones que solucionan el correcto agrupamiento por survey, codigo contenedor y service
				2.1 - Se cambia comparacion a k>0 para detectar cambio en grupo para excel en report2
				2.2 - Se agregan ord_codigo y comments de order_estimates a report2
				2.3 - Se complementa detalle de nombre de deposito y descripcion de service para planilla resume
				2.4 - Se agrega funcionalidad para confirmacion de facturas
				2.5 - Se corrige acumulacion de detalle trans-survey_type en metodo create
*/
class InvoiceController extends AppController
{	
	var $name       = 'Invoices';
	var $uses       = array('Order','Owner','Container','SurveyType','ServiceType','OrderContainer', 'OrderDepot', 'OrderEstimate', 'OwnerPrice', 'Country', 'Depot', 'Surveyor');
	var $helpers    = array('Html','Javascript','Ajax','jsCalendar');
	var $estado_informada         = 'N';
	var $container_info = array();
	
	function index() {
		$this->checkSession();
		$this->layout = 'main';
		$owners = $this->Owner->generateList();
		if (!empty($owners)) {
				$this->set('owners', array('A'=>'Select an Owner')+$owners);
		}
		/*$survey_types = $this->SurveyType->generateList();
		if (!empty($survey_types)) {
				$this->set('survey_types', array('A'=>'Select the Survey Type')+$survey_types);
		}*/
		/*$service_types = $this->ServiceType->generateList();
		if (!empty($service_types)) {
				$this->set('service_types', array('A'=>'Select the Service Type')+$service_types);
		}*/
		$countries = $this->Country->generateList();
		if (!empty($countries)) {
				$this->set('countries', array('A'=>'Select the Country')+$countries);
		}
		$this->set($this->data['Invoice']['from_date'], '2009-06-06');
		$this->set($this->data['Invoice']['to_date'], '2009-07-07');
	}
	
	function confirm($type) {
		$this->layout = 'ajax';
		//echo $type;
		if(!empty($this->data)) {
			$ERROR = 0;
			//pr($this->data);
			//echo $this->data['Invoice']['orders_'.$type];
			$orders_array = explode(",", $this->data['Invoice']['orders_'.$type]);
			//pr($orders_array);
			foreach ($orders_array as $order_id) {
				$ERROR_MSG = $this->Order->invoiceConfirm($order_id);
				if (strlen($ERROR_MSG) > 6) {
					$ERROR = 1;
					break;
				}
			}
			if ($ERROR == 0) {
				$this->set('message', $ERROR_MSG);
			} else {
				$this->set('message', $ERROR_MSG);
			}
		}
	}
	
	function create() {
		$this->checkSession();
		$usr_profile = $this->Session->read('Profile');
		
		$this->layout = 'ajax';
		$ERROR = 0;
		if (!empty($this->data)) {
			$owner_searched = $this->data['Invoice']['owner'];
			//$survey_type = $this->data['Invoice']['sut_codigo'];
			$cou_codigo = $this->data['Invoice']['cou_codigo'];
			$from_date = $this->data['Invoice']['from_date'];
			$to_date = $this->data['Invoice']['to_date'];
			$order_status = $this->data['Invoice']['order_status'];
			$orders_array = array();
			
			if ($owner_searched == 'A') {
				$ERROR = 1;
				$this->set('ERROR_DESC', 'An Owner must be specified');
				$this->render('error','ajax');
				
			} 
			
			if ($cou_codigo == 'A') {
				$ERROR = 2;
				$this->set('ERROR_DESC', 'A Country must be specified');
				$this->render('error','ajax');
			
			}
			
			if ($ERROR == 0) {		
				//$owner_orders = $this->Order->getOrdersForInvoice($owner_searched, $survey_type, $cou_codigo, $from_date, $to_date);
				$owner_orders = $this->Order->getOrdersForInvoice($owner_searched, $cou_codigo, $from_date, $to_date, $order_status);

				$this->set('own_codigo', $owner_searched);
				$this->set('cou_codigo', $cou_codigo);
				$this->set('from_date',$from_date);
				$this->set('to_date', $to_date);
				
				if (!array_key_exists('0', $owner_orders)) {
					$ERROR = 3;
					$this->set('ERROR_DESC', 'There were no orders');
					$this->render('error','ajax');
				} 
			}
			
			if ($ERROR == 0) {
				$order_global = array();
				//$order_containers_resume = array();
				$order_containers_for_invoice = array();
				$total_price = 0;
				//pr(count($owner_orders));
				
				//inicio modificaciones para agrupar por deposito independiente de la orden
				$containers = array();
				$containers_count = 0;
				foreach ($owner_orders as $order) {
					$pre_sut_codigo = $order['Order']['sut_codigo'];
					//pr($order['OrderContainer']);
					foreach ($order['OrderContainer'] as $orderContainer) {
						//$orderContainer['sut_codigo'] = $pre_sut_codigo;
						//pr($orderContainer);
						$pre_depot = $orderContainer['dep_codigo']; 
						$pre_cot_codigo = $orderContainer['cot_codigo']; 
						$pre_set_codigo = $orderContainer['set_codigo'];
						$pre_key = $pre_depot.$pre_set_codigo.$pre_cot_codigo.$containers_count;
						if (!array_key_exists($pre_sut_codigo, $containers)) {
							$containers[$pre_sut_codigo][$pre_key] = $orderContainer;
						} else {
							if (!array_key_exists($pre_key, $containers[$pre_sut_codigo])) {
								$containers[$pre_sut_codigo][$pre_key] = $orderContainer;
							} else {
								$containers[$pre_sut_codigo][$pre_key] += $orderContainer;
							} 
						}
						$containers_count++;
					}
				}
				
				ksort($containers[$pre_sut_codigo]);
				//pr($containers);
				//pr($owner_orders);
				
				$own_codigo = $owner_searched;
				
					//limpia arreglo
					//$order_containers_resume = array();	
				$order_global = array();
					//$quantity = 0;
					//$survey_price = 0;
					//$price = 0;
					//$total_price = 0;
					//$i=0;

				foreach ($containers as $survey=>$containers) {
					$order_containers_resume = array();
					$total_price  = 0;
					$quantity     = 0;
					$survey_price = 0;
					$price        = 0;
					//pr($survey);
					$orders_array[$survey] = array();
					//pr($containers);
					//$survey_type = $containers['sut_codigo'];
					//pr($order['OrderContainer']);
					foreach ($containers as $container) {
						//pr($container);
						//echo array_search($container['ord_codigo'], $orders_array[$survey]);
						if (array_search($container['ord_codigo'], $orders_array[$survey]) === false) {
							array_push($orders_array[$survey], $container['ord_codigo']);
						}
						
						//pr($orders_array);
						$dep_codigo = $container['dep_codigo'];
						$set_codigo = $container['set_codigo'];
						$cod_codigo = $container['cod_codigo'];
						
						if ($usr_profile == 'admin') {
							$survey_price = $this->OwnerPrice->field('opl_valor', "own_codigo = '$own_codigo' and set_codigo = '$set_codigo' and cou_codigo = '$cou_codigo'");
						} else {
							$survey_price = 0;
						}
						
						//echo "------------------------<br />";		//PRF: perfect debug
						//echo "iteracion: ".$i."<br />";				//PRF: perfect debug
						//echo "dep_codigo: ". $dep_codigo."<br />";	//PRF: perfect debug
						//echo "set_codigo: ". $set_codigo."<br />";	//PRF: perfect debug
						//echo "cod_codigo: ". $cod_codigo."<br />";	//PRF: perfect debug
						
						//pr($order_containers_resume);
						//$res1 = $this->in_arrayr($dep_codigo, $order_containers_resume); 
						//$res2 = $this->in_arrayr($set_codigo, $order_containers_resume); 
						$res1 = array_key_exists($dep_codigo, $order_containers_resume);
						$res2 = @array_key_exists($set_codigo, $order_containers_resume[$dep_codigo]);
						//echo "res1: ".$res1."<br />";
						//echo "res2: ".$res2."<br />"; 
						
						
						if ($res1 and !$res2) {
							//echo "encontre depot<br />"; //PRF: perfect debug
							$quantity = 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						} elseif ($res1 and $res2) {
							//echo "encontre depot con service<br />"; //PRF: perfect debug
							$quantity = $order_containers_resume[$dep_codigo][$set_codigo]['Quantity'] + 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						} else {
							//echo "no encontre <br />"; //PRF: perfect debug
							$quantity = 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						}
						//pr($order_containers_resume); //PRF: perfect debug
						//$i++;
						$total_price += $uprice;
					}
					//Si el arreglo order_global ya tiene detalle para  ese survey se debe adicionar
					if (! array_key_exists($survey, $order_global)) {
						//echo "creando order_global para survey type ". $survey."<br />";	//PRF: perfect debug
						$order_global[$survey] = $order_containers_resume;
					} else {
						//echo "agregando resumen para order global con survey type ". $survey."<br />"; //PRF: perfect debug
						//pr($order_containers_resume);
						//$order_global[$survey_type] += $order_containers_resume;
						//array_push($order_global[$survey_type], $order_containers_resume);
						$order_global[$survey] = $order_containers_resume;
						//pr($order_global[$survey]);
					}
					
				}
				//pr($order_global); //PRF: perfect debug
				//break;
				/*foreach ($owner_orders as $order) {
					$survey_type = $order['SurveyType']['sut_codigo'];
					$own_codigo  = $order['Order']['own_codigo'];
					
					//limpia arreglo
					$order_containers_resume = array();	
					$quantity = 0;
					$survey_price = 0;
					$price = 0;
					$total_price = 0;
					
					$i=0;
					//pr($order['OrderContainer']);
					foreach ($order['OrderContainer'] as $order_container) {
						$dep_codigo = $order_container['dep_codigo'];
						$set_codigo = $order_container['set_codigo'];
						
						if ($usr_profile == 'admin') {
							$survey_price = $this->OwnerPrice->field('opl_valor', "own_codigo = '$own_codigo' and set_codigo = '$set_codigo' and cou_codigo = '$cou_codigo'");
						} else {
							$survey_price = 0;
						}
						// -- PRF: tratar registro de box y machine en factura
						//if ($order_container['oco_tipo'] == 2) {
						//	$survey_price_mach = $this->OwnerPrice->field('opl_valor', "own_codigo = '$own_codigo' and set_codigo = '$set_codigo'");
						//	$survey_price += $survey_price_mach;
						//}
						
						//echo "iteracion: ".$i."<br />";
						//echo "dep_codigo: ". $dep_codigo."<br />";
						//echo "set_codigo: ". $set_codigo."<br />";
						//echo "buscando en: <br />";
						//echo $ocr_hash;
						//pr($order_containers_resume);
						//$res1 = $this->in_arrayr($dep_codigo, $order_containers_resume); 
						//$res2 = $this->in_arrayr($set_codigo, $order_containers_resume); 
						$res1 = array_key_exists($dep_codigo, $order_containers_resume);
						$res2 = @array_key_exists($set_codigo, $order_containers_resume[$dep_codigo]);
						//echo "res1: ".$res1."<br />";
						//echo "res2: ".$res2."<br />";
						
						
						if ($res1 and !$res2) {
							//echo "encontre depot<br />";
							$quantity = 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						} elseif ($res1 and $res2) {
							//echo "encontre depot con service<br />";
							$quantity = $order_containers_resume[$dep_codigo][$set_codigo]['Quantity'] + 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						} else {
							//echo "no encontre <br />";
							$quantity = 1;
							if(empty($survey_price)) {
								$survey_price = "<span style='color:red'>No cost defined for this survey type</span>";
								$uprice = 0;
								$price = 0;
							} else {
								$uprice = $survey_price;
								$price = $survey_price*$quantity;
							}
							$order_containers_resume[$dep_codigo][$set_codigo] = array('Quantity' => $quantity, 'UnitPrice' => $survey_price, 'SubTotalPrice' => $price);
						}
						$i++;
						//echo "1: ".$total_price."<br />";
						//echo "2: ".$uprice."<br />";
						$total_price += $uprice;
						//echo "subtotal: ".$total_price."<br />";
					}
					
					//Si el arreglo order_global ya tiene detalle para  ese survey se debe adicionar
					if (! array_key_exists($survey_type, $order_global)) {
						$order_global[$survey_type] = $order_containers_resume;
					} else {
						$order_global[$survey_type] += $order_containers_resume;
					}
					
				}*/

					
				$this->set('order_global',$order_global);
				$this->set('order_containers',$order_containers_resume);
				$this->set('total_price',$total_price);
				$this->set('own_nombre', $owner_orders['0']['Owner']['own_nombre']);
				$this->set('own_contacto', $owner_orders['0']['Owner']['own_contacto']);
				$this->set('own_direccion1', $owner_orders['0']['Owner']['own_direccion1']);
				$this->set('own_telefono1', $owner_orders['0']['Owner']['own_telefono1']);
				$this->set('own_mail', $owner_orders['0']['Owner']['own_mail']);
				$this->set('orders',$owner_orders);
				$this->set('order_status', $order_status);
				$this->set('orders_array', $orders_array);
				$this->set('usr_profile', $usr_profile);
				$this->render('invoice','ajax');
			}
		}
	}
	
	function report($own_codigo, $cou_codigo, $depot, $from_date, $to_date, $service) {
		$this->checkSession();
		$this->layout = 'blank';
		//require (VENDORS . "class-excel-xml.inc.php");
		require (VENDORS . "php-excel.class.php");
	
		if ($from_date == '--') {$from_date = '';}
		if ($to_date == '--') {$to_date = '';}
		
		$owner_orders = $this->Order->getOrdersForReport($own_codigo, $cou_codigo, $from_date, $to_date);
		//array $rows;
		$k = 1;
		//echo "owner_orders";
		//pr($owner_orders);
		foreach ($owner_orders as $order) {
			$ord_codigo = $order['Order']['ord_codigo'];
			$orders_depots = $this->OrderDepot->getDepotsForReport($ord_codigo, $depot);
			//echo "orders_depots";
			//pr($orders_depots);
			foreach ($orders_depots as $order_depot) {
				$dep_codigo = $order_depot['OrderDepot']['dep_codigo'];
				$orders_containers = $this->OrderContainer->getContainersForReport($ord_codigo, $service);
				//echo "orders_containers";
				//pr($orders_containers);
				foreach ($orders_containers as $order_container) {
					$cod_codigo = $order_container['OrderContainer']['cod_codigo'];
					
					// %todo llevar esto al modelo y poner if para que agregue busqueda con "-MACH"
					/*$pattrn = '/^.*-MACH$/';
					if (preg_match($pattrn, $cod_codigo)) {
						$cod_codigo = preg_replace('/-MACH/', '', $cod_codigo);
					}*/
					$container_info = $this->Container->find("cod_codigo = '$cod_codigo'", //conditions
															 array(), //fields
															 'cod_codigo ASC', //order
															 '-1' //recursive
															 );
															 
					$container_id = $container_info['Container']['cod_codigo'];
					$con_manuf = $container_info['Container']['con_manuf'];
					$survey_date = $container_info['Container']['con_onhdate'];
					$container_type = $container_info['Container']['cot_codigo'];
					$container_mgw = $container_info['Container']['con_mgw'];
					$container_tare = $container_info['Container']['con_tare'];
					
																				 
					$container_estimates = $this->OrderEstimate->findAll("ord_codigo = '$ord_codigo' and
																	  cod_codigo like '$cod_codigo' and
																	  dep_codigo = '$dep_codigo'", //conditions
																	array(), //fields
																	'cod_codigo ASC', //order
																	'2', //limit
																	'', //page
																	'-1' //recursive
																	);
					

					//echo "container_estimates";
					//pr($container_estimates);
					foreach ($container_estimates as $container_estimate) {
						$container_id = $container_estimate['OrderEstimate']['cod_codigo'];  // de nuevo container_id por si tiene concatenado "-MACH"
						$original_est = $container_estimate['OrderEstimate']['est_original'];
						$final_est = $container_estimate['OrderEstimate']['est_final'];
						$saving = $container_estimate['OrderEstimate']['est_saving'];
						$reducc = "%";
						$id = $container_estimate['OrderEstimate']['est_id'];
						$cl = $container_estimate['OrderEstimate']['est_cl'];
						$wt =  $container_estimate['OrderEstimate']['est_wt'];
						$ir = $container_estimate['OrderEstimate']['est_ir'];
						$pti = $container_estimate['OrderEstimate']['est_pti'];
						$line_account = $container_estimate['OrderEstimate']['est_line'];
						$user_account = $container_estimate['OrderEstimate']['est_client'];
						$third_party = $container_estimate['OrderEstimate']['est_thirds'];
						$approved = $container_estimate['OrderEstimate']['est_approved'];
						$repair_suggested = $container_estimate['OrderEstimate']['est_original'];
						$document = $container_estimate['OrderEstimate']['est_repair_suggested'];
						$cod_author = "autor";
						
						$rows[$k] = array($k, $container_id, $con_manuf, $survey_date, 
										  $container_type, $container_mgw, $container_tare, 
										  $original_est, $final_est, $saving, $reducc, 
										  $id, $cl, $wt, $ir, $pti, $line_account, $user_account, 
										  $third_party, $approved, $repair_suggested, $document, 
										  $cod_author);
						$k++;	
					
					}
				}

			}
				
		}
		//pr($rows);
		//break;
		$resumen = array (
		    1 => array ("Client      :", $this->Owner->field("own_nombre","own_codigo = '$own_codigo'")),
		    2 => array ("Country     :", $this->Country->field("cou_nombre","cou_codigo = '$cou_codigo'")),
			3 => array ("Depot       :", $this->Depot->field("dep_nombre","dep_codigo = '$depot'")),
			4 => array ("Date        :", date('l\, F jS\, Y')),
			5 => array ("Survey Type :", "TODAS! por ahora"),
			7 => array ("",""),
			8 => array ("","")
		    );
		
		$conjunto = array (
		    1 => array ("DRIVES"));
		   
		$cabecera = array (
			1 => array ("No.","Container No.","Manufd.","Survey Date","Type","Mgw","Tare","Original Est","Final Estim","Save","Reducc %","ID","CL","WT","IR","PTI","Line account","User Account","3rd Party Claim","Aprov","Rep Suggd","Photos","Cod Autor"));
		
		$detalle = $rows;
		
		$xls = new Excel_XML;
		$xls->addArray ( $resumen );
		$xls->addArray ( $conjunto );
		$xls->addArray ( $cabecera );
		$xls->addArray ( $detalle );
		$xls->generateXML ("reporte_servicontainers");
	}
		
	function report2($own_codigo, $cou_codigo, $depot, $state, $from_date, $to_date, $survey_type) {
		$this->checkSession();
		$this->layout = 'blank';
		//require (VENDORS . "class-excel-xml.inc.php");
		require (VENDORS . "php-excel.class.php");
	
		if ($from_date == '--') {$from_date = '';}
		if ($to_date == '--') {$to_date = '';}
		
		$literal_month = "Undated";
		
		if (strlen($from_date) > 6) {
			$year = substr($from_date, 0, 4);
			$month = substr($from_date, 5, 2);
			$literal_month = date ('F', mktime(0,0,0,$month, 1, $year));
		}
			
		//$owner_orders = $this->Order->getOrdersForReport($own_codigo, $cou_codigo, $from_date, $to_date, $survey_type);
		$owner_orders = $this->Order->getOrdersForReport2($own_codigo, $cou_codigo, $from_date, $to_date, $survey_type, $state);
		$dep_nombre = $this->Depot->field("dep_nombre","dep_codigo = '$depot'");
		
		$rows = array();
		//$k = 1;
		$rowcount = 0;
		//echo "owner_orders";
		//pr($owner_orders);
		foreach ($owner_orders as $order) {
			$ord_codigo = $order['Order']['ord_codigo'];
			$orders_depots = $this->OrderDepot->getDepotsForReport($ord_codigo, $depot);
			//$orders_depots = $this->OrderDepot->getDepotsForReport($ord_codigo);
			//echo "orders_depots";
			//pr($orders_depots);
			foreach ($orders_depots as $order_depot) {
				$dep_codigo = $order_depot['OrderDepot']['dep_codigo'];
				//$orders_containers = $this->OrderContainer->getContainersForReport($ord_codigo, $service);
				$orders_containers = $this->OrderContainer->getContainersForReport2($ord_codigo);
				//echo "orders_containers";
				//pr($orders_containers);
				//$container_type_anterior = '';
				foreach ($orders_containers as $order_container) {
					//echo "order_container";
					//pr($order_container);
					$cod_codigo = $order_container['OrderContainer']['cod_codigo'];
					$cod_codigo_original = $cod_codigo;
					//1.5 para quitar el sufijo en los service type
					$set_codigo = preg_replace('/-.$/','', $order_container['OrderContainer']['set_codigo']);
					// %todo llevar esto al modelo y poner if para que agregue busqueda con "-MACH"
					$pattrn = '/^.*-MACH$/';
					if (preg_match($pattrn, $cod_codigo)) {
						$cod_codigo = preg_replace('/-MACH/', '', $cod_codigo);
					}
					$container_info = $this->Container->find("cod_codigo = '$cod_codigo'", //conditions
															 array(), //fields
															 //'cod_codigo ASC', 
															 'cot_codigo ASC', //order
															 '-1' //recursive
															 );
															 
					$container_id = $container_info['Container']['cod_codigo'];
					$con_manuf = $container_info['Container']['con_manuf'];
					$survey_date = $container_info['Container']['con_onhdate'];
					$container_type = $container_info['Container']['cot_codigo'];
					$container_mgw = $container_info['Container']['con_mgw'];
					$container_tare = $container_info['Container']['con_tare'];
					
																				 
					$container_estimates = $this->OrderEstimate->findAll("ord_codigo = '$ord_codigo' and
																		  cod_codigo = '$cod_codigo_original'", //conditions
																		  array(), //fields
																		  'cod_codigo ASC', //order
																		  '2', //limit
																		  '', //page
																		  '-1' //recursive
																		);
					

					//echo "container_estimates";
					//pr($container_estimates);
					foreach ($container_estimates as $container_estimate) {
						//echo "container_estimate";
						//pr($container_estimate);
						$container_id = $cod_codigo;  // se reemplaza por cod_codigo que sabemos que no tiene -MACH
						$original_est = $container_estimate['OrderEstimate']['est_original'];
						$final_est = $container_estimate['OrderEstimate']['est_final'];
						$saving = $container_estimate['OrderEstimate']['est_saving'];
						$reducc = "=100-(RC[-2]*100/RC[-3])";
						$id = $container_estimate['OrderEstimate']['est_id'];
						$cl = $container_estimate['OrderEstimate']['est_cl'];
						$wt =  $container_estimate['OrderEstimate']['est_wt'];
						$ir = $container_estimate['OrderEstimate']['est_ir'];
						$pti = $container_estimate['OrderEstimate']['est_pti'];
						$line_account = $container_estimate['OrderEstimate']['est_line'];
						$user_account = $container_estimate['OrderEstimate']['est_client'];
						$third_party = $container_estimate['OrderEstimate']['est_thirds'];
						if ($container_estimate['OrderEstimate']['est_approved'] == 0) {
							$approved = 'NO';
						} else {
							$approved = 'YES';
						}
						if ($container_estimate['OrderEstimate']['est_repair_suggested'] == 0) {
							$repair_suggested = 'NO';
						} else {
							$repair_suggested = 'YES';
						}
						if ($container_estimate['OrderEstimate']['est_photos'] == 0) {
							$document = 'NO';
						} else {
							$document = 'YES';
						}
						$est_surveyor = $container_estimate['OrderEstimate']['est_surveyor'];
						$cod_author = $this->Surveyor->field("sur_nombre", "sur_codigo = '$est_surveyor'");
						
						//2.2
						$est_comments = $container_estimate['OrderEstimate']['est_comment'];
						$est_ord_codigo = $container_estimate['OrderEstimate']['ord_codigo'];
						
						/* RESUMEN GRUPO
						if (($k > 1) and (($container_type != $container_type_anterior) or ($set_codigo != $set_codigo_anterior))) {
							
							$rows[$k] = array("","");
							$k++;

							$offset = $rowcount+1;
							
							$rows[$k] = array("TOTAL", $container_type_anterior . "-" . $set_codigo_anterior, $rowcount, "", "", "", "", "", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)");
							$k++;
							$rows[$k] = array("","");
							$k++;
							$rows[$k] = array("","");
							$rowcount = 0;
						}
						*/
						$key = $depot.$container_type.$set_codigo;
						$rowcount++;
						if (!array_key_exists($key, $rows)) {
							$rows[$key] = array($container_id, $container_type,
											  $set_codigo, $con_manuf, $survey_date, $container_mgw, $container_tare, 
											  $original_est, $final_est, $saving, $reducc, 
											  $id, $cl, $wt, $ir, $pti, $line_account, $user_account, 
											  $third_party, $approved, $repair_suggested, $document, 
											  $cod_author, $est_comments, $est_ord_codigo);
						} else {
							$key = $depot.$container_type.$set_codigo.$rowcount;
							$rows[$key] = array($container_id, $container_type,
											  $set_codigo, $con_manuf, $survey_date, $container_mgw, $container_tare, 
											  $original_est, $final_est, $saving, $reducc, 
											  $id, $cl, $wt, $ir, $pti, $line_account, $user_account, 
											  $third_party, $approved, $repair_suggested, $document, 
											  $cod_author, $est_comments, $est_ord_codigo);
						}
						
						//$k++;
						
						//$o = 0;
						//$container_type_anterior = $container_type;
						//$set_codigo_anterior = $set_codigo;
						
					}
					
				}
					
			}
			//$rows = array();
				
		}
		//pr($rows);
		//break;
		
		/* RESUMEN GRUPO
		if (($k > 1) and (($container_type != $container_type_anterior) or ($set_codigo != $set_codigo_anterior))) {
			
			$rows[$k] = array("","");
			$k++;

			$offset = $rowcount+1;
			
			$rows[$k] = array("TOTAL", $container_type_anterior . "-" . $set_codigo_anterior, $rowcount, "", "", "", "", "", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)");
			$k++;
			$rows[$k] = array("","");
			$k++;
			$rows[$k] = array("","");
			$rowcount = 0;
		}
		*/
		/* ULTIMO RESUMEN
		$k++;
		$rows[$k] = array("","");
		$k++;
		$offset = $rowcount+1;
		$rows[$k] = array("TOTAL", $container_type_anterior . "-" . $set_codigo_anterior, $rowcount, "", "", "", "", "", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)", "=SUM(R[-".$offset."]C:R[-2]C)");
		$k++;
		$rows[$k] = array("","");
		$rowcount = 0;
		*/
		/*
		for ($i = 1; $i < count($rows); $i++) {
			echo $i."<br />";
			print_r($rows[$i]);
			echo "<br />";
		}*/
		
		ksort($rows);
		//pr($rows);
		//echo $rowcount;
		//break;
		
		$ws_rows = array();
		$ws_rowcount = 0;
		$ws_k = 0;
		$ws_container_type_anterior = '';
		$ws_set_codigo_anterior = '';
		foreach ($rows as $row) {
			
			$ws_container_type = $row[1];
			$ws_set_codigo = $row[2];
			//echo "depot: ".$row[1]." cot_codigo: ".$ws_container_type." set_codigo: ".$ws_set_codigo."<br />";
			if (($ws_k > 0) and (($ws_container_type != $ws_container_type_anterior) or ($ws_set_codigo != $ws_set_codigo_anterior))) {
				$ws_k++;
				$ws_rows[$ws_k] = array("","");
				$ws_k++;
				$ws_offset = $ws_rowcount+1;
				$ws_rows[$ws_k] = array("TOTAL", $ws_container_type_anterior . "-" . $ws_set_codigo_anterior, $ws_rowcount, "", "", "", "", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)");
				$ws_k++;
				$ws_rows[$ws_k] = array("","");
				$ws_k++;
				$ws_rows[$ws_k] = array("","");
				$ws_rowcount = 0;
			}
			
			$ws_rows[$ws_k] = $row;
			
			$ws_container_type_anterior = $ws_container_type;
			$ws_set_codigo_anterior = $ws_set_codigo;
			$ws_k++;
			$ws_rowcount++;
		}
		//RESUMEN ULTIMO GRUPO
		$ws_k++;
		$ws_rows[$ws_k] = array("","");
		$ws_k++;
		$ws_offset = $ws_rowcount+1;
		$ws_rows[$ws_k] = array("TOTAL", $ws_container_type_anterior . "-" . $ws_set_codigo_anterior, $ws_rowcount, "", "", "", "", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)", "=SUM(R[-".$ws_offset."]C:R[-2]C)");
		$ws_k++;
		$ws_rows[$ws_k] = array("","");
		//pr($ws_rows);
		//break;
		
		$own_nombre = $this->Owner->field("own_nombre","own_codigo = '$own_codigo'");
		$cou_nombre = $this->Country->field("cou_nombre","cou_codigo = '$cou_codigo'");
		$survey_type = preg_replace('/-.$/','', $survey_type);
				
		$resumen = array (
		    1 => array ("Client      :", $own_nombre),
		    2 => array ("Country     :", $cou_nombre),
			3 => array ("Depot       :", $dep_nombre),
			4 => array ("Date        :", date('l\, F jS\, Y')),
			5 => array ("Survey Type :", $survey_type),
			6 => array ("",""),
			7 => array ("","")
		    );
		   
		$cabecera = array (
			1 => array ("Container No.","Type","Service Type", "Manufd.","Survey Date","Mgw","Tare","Original Est","Final Estim","Save","Reducc %","ID","CL","WT","IR","PTI","Line account","User Account","3rd Party Claim","Approved","Rep Suggd","Photos","Surveyor","Comments","Order"));
		
		//$detalle = $rows;
		$detalle = $ws_rows;
		
		$xls = new Excel_XML('ISO-8859-1', true, 'Table1');
		$xls->addArray ( $resumen );
		//$xls->addArray ( $conjunto );
		$xls->addArray ( $cabecera );
		$xls->addArray ( $detalle );
		$xls->generateXML ($literal_month."-".$survey_type."-".$cou_nombre);
	}
	
	function resume($own_codigo, $cou_codigo, $state, $from_date, $to_date, $survey_type) {
		$this->checkSession();
		$usr_profile = $this->Session->read('Profile');
		$this->layout = 'blank';
		//require (VENDORS . "class-excel-xml.inc.php");
		require (VENDORS . "php-excel.class.php");
	
		if ($from_date == '--') {$from_date = '';}
		if ($to_date == '--') {$to_date = '';}
		
		$owner_orders = $this->Order->getOrdersForResume($own_codigo, $cou_codigo, $survey_type, $state, $from_date, $to_date);
		
		$survey_type_description = $this->SurveyType->getDescription($survey_type);
		$k = 1;
		//echo "owner_orders";
		//pr($owner_orders);
		//$this->set('usr_profile', $usr_profile);
		$total_q = 0;
		$total_price = 0;
		foreach ($owner_orders as $order) {
			$ord_codigo = $order['Order']['ord_codigo'];
			$orders_depots = $this->OrderDepot->getDepotsForResume($ord_codigo);
			//echo "orders_depots";
			//pr($orders_depots);
			foreach ($orders_depots as $order_depot) {
				$dep_codigo = $order_depot['OrderDepot']['dep_codigo'];
				$dep_nombre = $this->Depot->field('dep_nombre', "dep_codigo = '$dep_codigo'");
				$orders_services = $this->OrderContainer->getServicesForResume($ord_codigo, $dep_codigo);
				//echo "services for resume";
				foreach ($orders_services as $order_service) {
					$set_codigo = $order_service['order_containers']['set_codigo'];
					//$set_descripcion = $this->ServiceType->field('set_descripcion', "set_codigo = '$set_codigo'");
					$set_descripcion = $set_codigo." (".$this->ServiceType->field('set_descripcion', "set_codigo = '$set_codigo'").")";
					$q = $order_service[0]['q'];
					
					if ($usr_profile == 'admin') {
						$rate = $this->OwnerPrice->field('opl_valor', "own_codigo = '$own_codigo' and set_codigo = '$set_codigo' and cou_codigo = '$cou_codigo'");
					} else {
						$rate = 0;
					}
					
					/*if(empty($rate)) {
						$rate  = 0;
						$price = 0;
					} else {
						$price = $rate*$q;
					}*/
					$pre_key = $dep_codigo.$set_codigo.$k;
					//$pre_rows[$k] = array($pre_key=>array($dep_codigo, $set_codigo, $q, $rate, $price));
					//$pre_rows[$pre_key] = array($dep_codigo, $set_codigo, $q, $rate, $price);
					$pre_rows[$pre_key] = array($dep_nombre, $set_descripcion, $q, $rate);
					/*$total_q += $q;
					$total_price += $price;*/
					$k++;
				}

			}
				
		}
	
		ksort($pre_rows);
		//pr($pre_rows);
		//break;
		$rows = array();
		$j = 1;
		$dep = '';
		$serv = '';
		$qf = 0;
		foreach ($pre_rows as $row) {
			/*
				j : indice numerico de array de resumen de containers final, tb se usa para calcular offset para formula en excel
				dep : deposito al cual pertenece el container
				serv : servicio que se le aplico al container
				qf : cantidad final de containers de un grupo
				row[4] : cantidad x precio, se utiliza para formula en excel
			*/
			//pr($row);
			$row[4] = '=(RC[-2]*RC[-1])';
			if (($row[0] == $dep) and ($row[1] == $serv)) {
				// detecta si el container es del mismo grupo para deposito y servicio
				// luego suma la cantidad final
				// por ultimo llena array rows que contiene los grupos
				//echo "entre<br />";
				$row[2] += $qf;
				//$row[4] += $row[2]*$row[3];
				$rows[$j] = $row;
			} else {
				// si es parte de un nuevo grupo incrementa el indice y llena el array
				$j++;
				$rows[$j] = $row;
			}
			//actualiza valores desde ultimo registro leido
			$qf = $row[2];
			$dep = $row[0];
			$serv = $row[1];
		}
		
		//pr($rows);
		//break;
		$own_nombre = $this->Owner->field("own_nombre","own_codigo = '$own_codigo'");
		
		$resumen = array (
		    1 => array ($own_nombre),
		    2 => array ("Orders type", $survey_type_description),
			3 => array ("From", $from_date),
			4 => array ("To ", $to_date),
			5 => array ("",""),
			6 => array ("","")
		    );
		   
		$cabecera = array (
			1 => array ("DEPOT","SERVICE","QUANTITY","RATES","USS_TOTAL"));
		
		$detalle = $rows;
		
		$totales = array (
			//1 => array ("TOTALES","",$total_q, "", $total_price));
			1 => array ("TOTALES","","=SUM(R[-".($j-1)."]C:R[-1]C)", "", "=SUM(R[-".($j-1)."]C:R[-1]C)"));
		
		$xls = new Excel_XML('ISO-8859-1', true, 'Table1');
		$xls->addArray ( $resumen );
		$xls->addArray ( $cabecera );
		$xls->addArray ( $detalle );
		$xls->addArray ( $totales );
		$xls->generateXML ("resumen_servicontainers");
	}
}
?>